package com.hngm.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.hngm.bean.About;
import com.hngm.bean.Product;
import com.hngm.util.DBManager;
import com.hngm.util.QueryDataResult;
import com.hngm.util.StringUtil;

public class AboutDao {
	public ArrayList<About> queryAboutList() {
		// 定义设计师类型集合
		ArrayList<About> abList = new ArrayList<About>();
		// 定义查询的sql语句
		String sql = "select * from designer";
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sql);
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();
		// 遍历rs结果集
		try {
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String position = rs.getString("position");
				String designerpic = rs.getString("designerpic");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				About ab = new About(id, name, position, designerpic, creationtime, updatetime);
				abList.add(ab);
			}
		} catch (SQLException e) {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return abList;
	}

	/**
	 * 根据满足条件得设计师列表
	 */
	public ArrayList<About> queryAboutListByParm(String designername, String del) {
		ArrayList<About> aList = new ArrayList<About>();
		// 定义sql语句
		StringBuffer sb = new StringBuffer("select * from designer where 1=1");

		// 拼接查询条件
		if (StringUtil.isNotNullorEmpty(designername)) {
			sb.append(String.format(" and name like '%s'", "%" + designername + "%"));
		}
		if (StringUtil.isNotNullorEmpty(del)) {
			sb.append(String.format(" and del = '%s'", del));
		}
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sb.toString());
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String position = rs.getString("position");
				String designerpic = rs.getString("designerpic");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				About ab = new About(id, name, position, designerpic, creationtime, updatetime);
				aList.add(ab);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return aList;
	}

	public int queyrAboutListByParamCount(String designername, String del) {
		// 定义sql语句
		StringBuffer sb = new StringBuffer("select count(1) as count from designer where 1=1");

		// 拼接查询条件
		if (StringUtil.isNotNullorEmpty(designername)) {
			sb.append(String.format(" and name like '%s'", "%" + designername + "%"));
		}
		if (StringUtil.isNotNullorEmpty(del)) {
			sb.append(String.format(" and del = '%s'", del));
		}
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sb.toString());
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				return rs.getInt("count");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return 0;
	}

	public int insertDesigner(About p) {
		// 定义插入sql语句
		String sql = String.format("insert into designer(name,position,designerpic) values ('%s','%s','%s')", p.getName(),p.getPosition(),p.getDesignerpic());
		return DBManager.executeUpdate(sql);
	}

	public static int deldesigner(String designerId) {
		//定义删除产品的sql语句
		String sql = "delete from designer where id = " + designerId;
		return DBManager.executeUpdate(sql);
	}

	public int editDesigner(About p) {
		String sql =String.format("update designer set "
				+ "name='%s',"
				+ "position='%s',"
				+ "designerpic='%s' "
				+ "where id = %d",
				p.getName(),
				p.getPosition(),
				p.getDesignerpic(),
				p.getId());
		return DBManager.executeUpdate(sql);
	}
}
